﻿Imports NVelocity
Imports NVelocity.App
Imports NVelocity.Context

Imports System.Collections.Generic
Imports System.IO
Imports System.Globalization
Imports System.Data

Partial Class WebForm_ManageInsurance
    Inherits My.MyPage

    Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Me.Export()
    End Sub

    Protected Sub btnExportView_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportView.Click
        Me.ExportInsuranceView()
    End Sub

    Protected Sub btnList_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnList.Click
        Me.BindGV()
    End Sub

    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub

    Protected Sub BindGV()
        Me.gv.DataSource = Me.GetData()
        Me.gv.DataBind()
    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)
        'MyBase.VerifyRenderingInServerForm(control)
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            '顯示無資料訊息，不然一開始畫面整個是空的
            Me.gv.DataBind()

            'Me.lblUsage.Text = Me.Description
        End If
    End Sub

    Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
        Me.Container1.ActiveTab = Me.UploadTabPanel
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        If Me.DoUpload() = True Then
            Me.Container1.ActiveTab = Me.ListTabPanel
            Me.BindGV()
        End If
    End Sub

    
    Protected Sub gv_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles gv.Sorting
        Me.BindGV()
    End Sub
End Class

Partial Class WebForm_ManageInsurance
    Protected Function GetData() As Data.DataTable
        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        sql.AppendFormat("select ")
        sql.AppendFormat("vo.VOrganizationName,vil.TownName+vil.RegionName as VillageName,v.VolunteerName,v.VolunteerID,v.Birthday ")
        sql.AppendFormat(",vo.VOrganizationID,vo.OrganizationName, vo.DirectUOrganizationID ")
        sql.AppendFormat(",vi.StartDate as InsuranceStartDate ")
        sql.AppendFormat(",vi.EndDate as InsuranceEndDate ")
        sql.AppendFormat("from Volunteer v ")
        sql.AppendFormat("inner join (select v.VolunteerID, v.VOrganizationID, vo.OrganizationName, vo.VOrganizationName,vo.DirectUOrganizationID ")
        sql.AppendFormat("from VolunteerView v ")
        sql.AppendFormat("inner join VOrganizationView vo on v.UOrganizationID =vo.UOrganizationID ")

        ''已退出志工
        'sql.AppendFormat("union select ")
        'sql.AppendFormat("v.VolunteerID,null , null, null, null  from VolunteerInsurance v ")
        'sql.AppendFormat("left join VOrganizationMapping vom on v.VolunteerID = vom.VolunteerID ")
        'sql.AppendFormat("where vom.VolunteerID is null ")

        sql.AppendFormat(") vo on v.VolunteerID = vo.VolunteerID ")
        sql.AppendFormat("left join Village vil on v.VillageID = vil.RegionID ")
        sql.AppendFormat("left join VolunteerInsurance vi on v.VolunteerID = vi.VolunteerID ")

        'If Me.VolunteerQueryControl1.Region <> "" Then
        '    sql.AppendFormat("inner join Region r on r.RegionID = '{0}' and left(v.VillageID,3) = r.RegionID ", Me.VolunteerQueryControl1.Region)
        'End If

        'If Me.VolunteerQueryControl1.Region2 <> "" Then
        '    sql.AppendFormat("inner join Village ve on ve.RegionID = '{0}' and ve.RegionID = v.VillageID ", Me.VolunteerQueryControl1.Region2)
        'End If

        sql.AppendFormat("where 1=1 ")
        sql.AppendFormat("and v.Active='Y' ")
        If Me.txtInsuranceEndDate.Text <> "" Then
            sql.AppendFormat("and ( vi.EndDate <= '{0:yyyy/MM/dd}' or vi.EndDate is null ) ", Me.txtInsuranceEndDate.Value)
        End If

        If Me.VolunteerQueryControl1.VolunteerId <> "" Then
            sql.AppendFormat("and v.VolunteerID = '{0}' ", Me.VolunteerQueryControl1.VolunteerId)
        End If

        If Me.VolunteerQueryControl1.VolunteerName <> "" Then
            sql.AppendFormat("and v.VolunteerName like '%{0}%' ", Me.VolunteerQueryControl1.VolunteerName)
        End If

        If Me.VolunteerQueryControl1.VOrganizationID <> "0" Then
            sql.AppendFormat("and vo.VOrganizationID = {0} ", Me.VolunteerQueryControl1.VOrganizationID)
        End If

        If Me.VolunteerQueryControl1.OrganizationID <> "0" Then
            sql.AppendFormat("and vo.DirectUOrganizationID = '{0}' ", Me.VolunteerQueryControl1.OrganizationID)
        End If

        sql.AppendFormat(Me.gv.OrderBySQL)
        Return dat.GetData(sql)
    End Function

    Protected Sub ExportInsuranceView()
        Dim vcList As New List(Of InsuranceView)
        Dim vc As New InsuranceView
        Dim dt As System.Data.DataTable

        Dim i As Integer = 1

        dt = Me.GetData()

        For Each dr As Data.DataRow In dt.Rows
            vc = New InsuranceView

            vc.No = i.ToString()
            vc.VOrganizationName = dr("VOrganizationName").ToString()
            vc.VillageName = dr("VillageName").ToString()
            vc.VolunteerName = dr("VolunteerName").ToString()
            vc.VolunteerID = dr("VolunteerID").ToString()
            'vc.Birthday = Convert.ToDateTime(dr("Birthday").ToString())
            vc.Birthday = dr("Birthday").ToString()
            vc.InsuranceStartDate = dr("InsuranceStartDate").ToString()
            vc.InsuranceEndDate = dr("InsuranceEndDate").ToString()

            vcList.Add(vc)

            i = i + 1

        Next

        Dim fileName As String = "InsuranceViewList.xls"
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName)

        Dim velocity As New VelocityEngine
        Dim props As New Commons.Collections.ExtendedProperties

        'fix bug
        props.AddProperty("file.resource.loader.path", Server.MapPath("~\App_Data"))
        props.AddProperty("input.encoding", "utf-8")
        props.AddProperty("output.encoding", "utf-8")
        velocity.Init(props)

        Dim template As Template = velocity.GetTemplate("志工保險名單.xml")

        Dim context As New VelocityContext
        context.Put("OrganizationName", Me.VolunteerQueryControl1.VOrganizationName)
        context.Put("ExpandRow", 2 + i)
        context.Put("InsuranceViewList", vcList)
        context.Put("User", clsAuth.GetUserID())
        context.Put("DownloadDate", Today.Year.ToString() & "-" & Today.Month.ToString() & "-" & Today.Day.ToString() & "T" & Now.Hour.ToString() & ":" & Now.Minute.ToString() & ":" & Now.Second.ToString() & "Z")
        context.Put("IP", MyObj.getIP())
        Dim writer As New IO.StringWriter
        template.Merge(context, writer)

        Response.Write(writer.GetStringBuilder().ToString())

        Response.End()

    End Sub

    Protected Sub Export()
        'Dim gv As New GridView

        'gv.AutoGenerateColumns = False


        'MyObj.AddBoundField(gv, "VOrganizationName", " 隸屬志工隊")
        'MyObj.AddBoundField(gv, "VillageName", "區與里")
        'MyObj.AddBoundField(gv, "VolunteerName", "姓名")
        'MyObj.AddBoundField(gv, "VolunteerID", "身分證字號")
        'MyObj.AddBoundField(gv, "Birthday", "出生年月日")
        'MyObj.AddBoundField(gv, "InsuranceStartDate", "保險開始日")
        'MyObj.AddBoundField(gv, "InsuranceEndDate", "保險到期日")

        'gv.DataSource = Me.GetData()
        'gv.DataBind()

        'If gv.Rows.Count <= 0 Then
        '    MyObj.ShowMessage(Me.GetLocalResourceObject("Export_NoData"))
        '    Return
        'Else
        '    gv.Page = Me.Page
        '    MyObj.ExportToExcel(gv, "Insurance.xls")
        'End If
        Dim dt As DataTable = GetData()

        If dt.Rows.Count > 0 Then

            dt.Columns.Remove("VOrganizationID")
            dt.Columns.Remove("DirectUOrganizationID")
            dt.Columns.Remove("OrganizationName")


            For i As Integer = 0 To dt.Columns.Count - 1
                If dt.Columns(i).ColumnName = "VOrganizationName" Then

                    dt.Columns(i).ColumnName = "隸屬志工隊"

                ElseIf dt.Columns(i).ColumnName = "VillageName" Then


                    dt.Columns(i).ColumnName = "區與里"

                ElseIf dt.Columns(i).ColumnName = "VolunteerName" Then

                    dt.Columns(i).ColumnName = "姓名"

                ElseIf dt.Columns(i).ColumnName = "VolunteerID" Then

                    dt.Columns(i).ColumnName = "身分證字號"

                ElseIf dt.Columns(i).ColumnName = "Birthday" Then

                    dt.Columns(i).ColumnName = "出生年月日"

                ElseIf dt.Columns(i).ColumnName = "InsuranceStartDate" Then

                    dt.Columns(i).ColumnName = "保險開始日"

                ElseIf dt.Columns(i).ColumnName = "InsuranceEndDate" Then

                    dt.Columns(i).ColumnName = "保險到期日"

                End If
            Next
            Dim ms As MemoryStream = TryCast(DataTableRenderToExcel.RenderDataTableToExcelSetSheet(dt, "Insurance"), MemoryStream)

            ' 設定強制下載標頭。 
            Response.AddHeader("Content-Disposition", String.Format("attachment; filename=Insurance.xls"))
            ' 輸出檔案。 
            Response.BinaryWrite(ms.ToArray())

            ms.Close()
            ms.Dispose()
        Else
            MyObj.ShowMessage("您選擇的資料類型沒有資料可供匯出!")
        End If
    End Sub

    Protected Function DoUpload() As Boolean
        If Me.fuInsurance.HasFile = False Then
            MyObj.ShowMessage(Me.GetLocalResourceObject("DoUplaod_NoFile"))
            Return False
        End If

        Dim tempPath As String = Path.Combine(Path.GetTempPath(), Path.GetTempFileName())

        Me.fuInsurance.SaveAs(tempPath)

        'Dim connString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", tempPath)
        Dim dt As New Data.DataTable

        'Using oleConnection As New Data.OleDb.OleDbConnection(connString)
        '    Try
        '        Dim sql As New MyStringBuilder

        '        sql.AppendFormat("select * from [Insurance$] ")

        '        oleConnection.Open()

        '        Dim da As New Data.OleDb.OleDbDataAdapter
        '        da.SelectCommand = New Data.OleDb.OleDbCommand(sql.ToString(), oleConnection)

        '        da.Fill(dt)
        '    Catch
        '        MyObj.ShowMessage("格式錯誤，或工作表名稱必須為 Insurance ")
        '        Return False
        '    End Try
        'End Using
        Dim ExcelImportCheck As New ExcelImportCheck()
        '檢查工作表名稱
        If ExcelImportCheck.LoadData(dt, tempPath, "Insurance") = False Then
            Return False
        End If


        Dim dat As New clsData
        
        Using scope As New Transactions.TransactionScope
            For Each dr As Data.DataRow In dt.Rows
                Try

                    Dim sql As New MyStringBuilder

                    '     sql.Length = 0
                    sql.AppendFormat("delete VolunteerInsurance ")
                    sql.AppendFormat("where VolunteerID = '{0}' ;", dr("身分證字號"))



                    sql.AppendFormat("insert VolunteerInsurance (VolunteerID,StartDate,EndDate,InsuranceType,InsuranceOrg,Company,MoneyLevel,Charge,Active,CreateDate,CreateBy) select ")
                    sql.AppendFormat("'{0}' ", dr("身分證字號"))

                    If TypeOf dr("保險開始日") Is DBNull Then
                        sql.AppendFormat(",{0} ", "null")
                    Else
                        sql.AppendFormat(",'{0:yyyy/MM/dd}' ", DateTime.Parse(dr("保險開始日")))
                    End If

                    If TypeOf dr("保險到期日") Is DBNull Then
                        sql.AppendFormat(",{0} ", "null")

                        Dim message As String = String.Format("{0} 保險到期日格式有誤 ", dr("身分證字號"))

                        MyObj.WriteErrorLog(message)
                        Throw New System.Exception(message)
                    Else
                        sql.AppendFormat(",'{0:yyyy/MM/dd}' ", DateTime.Parse(dr("保險到期日")))
                    End If

                    sql.AppendFormat(",'{0}'", Me.ddlInsuranceType.SelectedValue)
                    sql.AppendFormat(",'{0}'", Me.txtlblInsuranceOrg.Text)
                    sql.AppendFormat(",'{0}'", Me.ddlCompany.SelectedValue)
                    sql.AppendFormat(",'{0}'", Me.txtMoneyLevel.Text)
                    sql.AppendFormat(",'{0}'", Me.txtCharge.Text)
                    sql.AppendFormat(",'{0}' ", "Y")
                    sql.AppendFormat(",{0} ", "getdate()")
                    sql.AppendFormat(",'{0}'; ", clsAuth.GetUserID())
                    'MyObj.WriteLog(sql.ToString())

                    dat.AccData(sql)

                Catch ex As System.Exception
                    MyObj.ShowMessage("匯入失敗, 請檢查log")
                    MyObj.WriteErrorLog(ex.Message)
                    Return False
                End Try

            Next

            scope.Complete()
        End Using

        MyObj.ShowMessage(Me.GetLocalResourceObject("DoUpload_Succ"))

        Return True
    End Function

    Public Class InsuranceView

        Private NoField As String
        Private VOrganizationNameField As String
        Private VillageNameField As String
        Private VolunteerNameField As String
        Private VolunteerIDField As String
        Private BirthdayField As String
        Private InsuranceStartDateField As String
        Private InsuranceEndDateField As String

        Public Property No() As String
            Get
                Return Me.NoField
            End Get
            Set(ByVal value As String)
                Me.NoField = value
            End Set
        End Property

        Public Property VOrganizationName() As String
            Get
                Return Me.VOrganizationNameField
            End Get
            Set(ByVal value As String)
                Me.VOrganizationNameField = value
            End Set
        End Property
        

        Public Property VillageName() As String
            Get
                Return Me.VillageNameField
            End Get
            Set(ByVal value As String)
                Me.VillageNameField = value
            End Set
        End Property

        Public Property VolunteerName() As String
            Get
                Return Me.VolunteerNameField
            End Get
            Set(ByVal value As String)
                Me.VolunteerNameField = value
            End Set
        End Property

        Public Property VolunteerID() As String
            Get
                Return Me.VolunteerIDField
            End Get
            Set(ByVal value As String)
                Me.VolunteerIDField = value
            End Set
        End Property

        Public Property Birthday() As String
            Get
                Return Me.BirthdayField
            End Get
            Set(ByVal value As String)
                Me.BirthdayField = value
            End Set
        End Property

        Public Property InsuranceStartDate() As String
            Get
                Return Me.InsuranceStartDateField
            End Get
            Set(ByVal value As String)
                Me.InsuranceStartDateField = value
            End Set
        End Property

        Public Property InsuranceEndDate() As String
            Get
                Return Me.InsuranceEndDateField
            End Get
            Set(ByVal value As String)
                Me.InsuranceEndDateField = value
            End Set
        End Property
        
    End Class
End Class